--    Program: 		CAPPOEXT_SUPPLIER_LIST.sql
--    Description:	Extract PO Supplier List information 
--    Revision:		1st     
--    Parameter: 	1. set of books short name e.g. SOB_C999

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

declare  

  l_processed_count_s 	number;
  l_rejected_count_s  	number;
  l_err_msg       		varchar2(2000);
  
  l_utl_file  	    	utl_file.file_type;
  l_filename 		    varchar2(255);
  l_pathname  		    varchar2(100);


--table data
  l_ngo_sob			    varchar2(20);
  l_code			    varchar2(30);
  l_set_of_books_id		number;
 
  cursor cur_vendor_sites (p_set_of_books_id number) is
  	select pvl.*
  	from   PO_VENDOR_LIST_ENTRIES pvl,
  	       PO_VENDOR_SITES_ALL pvs,
  	       HR_ORGANIZATION_INFORMATION hoi  	     
  	where  pvl.vendor_site_id = pvs.vendor_site_id
  	and    pvs.org_id = hoi.organization_id
  	and    hoi.org_information3 = to_char(p_set_of_books_id)
  	and    hoi.org_information_context = 'Operating Unit Information';
  		
begin
  l_pathname := '/tmp';
  l_filename := 'CAPPO_SUPPLIER_LIST.dat';
  l_utl_file := utl_file.fopen(l_pathname, l_filename, 'w');
  
  l_processed_count_s := 0;
  l_rejected_count_s  := 0;
                                         
  l_ngo_sob := '&1';
    
  begin
     select set_of_books_id
     into   l_set_of_books_id
     from   gl_sets_of_books
     where  upper(short_name) = upper(l_ngo_sob);     
   exception
     when others then
       dbms_output.put_line(l_ngo_sob||': set of books name not found');
       --raise;
  end;
  
	for cur in cur_vendor_sites(l_set_of_books_id) loop
		utl_file.put_line(l_utl_file,
				cur.vendor_list_entry_id || chr(9) ||
				cur.vendor_list_header_id || chr(9) ||
  				cur.LAST_UPDATE_DATE || chr(9) ||
  				cur.last_updated_by || chr(9) ||
  				cur.LAST_UPDATE_LOGIN || chr(9) ||
  				cur.CREATION_DATE || chr(9) ||
  				cur.created_by || chr(9) || 				
  				cur.VENDOR_ID || chr(9) ||
  				cur.VENDOR_SITE_ID || chr(9) ||
				cur.vendor_contact_id	|| chr(9) ||
  				cur.ATTRIBUTE_CATEGORY || chr(9) ||
  				cur.ATTRIBUTE1 || chr(9) ||
  				cur.ATTRIBUTE2 || chr(9) ||
  				cur.ATTRIBUTE3 || chr(9) ||
  				cur.ATTRIBUTE4 || chr(9) ||
  				cur.ATTRIBUTE5 || chr(9) ||
  				cur.ATTRIBUTE6 || chr(9) ||
  				cur.ATTRIBUTE7 || chr(9) ||
				cur.ATTRIBUTE8 || chr(9) ||
				cur.ATTRIBUTE9 || chr(9) ||
				cur.ATTRIBUTE10 || chr(9) ||
				cur.ATTRIBUTE11 || chr(9) ||
				cur.ATTRIBUTE12 || chr(9) ||
				cur.ATTRIBUTE13 || chr(9) ||
				cur.ATTRIBUTE14 || chr(9) ||
				cur.ATTRIBUTE15 || chr(9) ||
				cur.REQUEST_ID || chr(9) ||
				cur.PROGRAM_APPLICATION_ID || chr(9) ||
				cur.PROGRAM_ID || chr(9) ||
				cur.PROGRAM_UPDATE_DATE
			);
	
		l_processed_count_s := l_processed_count_s + 1;
	end loop;
 
  -- record to log table   
  if l_processed_count_s = 0 then
    l_rejected_count_s := l_rejected_count_s + 1;
    l_err_msg := l_ngo_sob || ': Supplier List entry not found';
  end if;    
      
  insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
  values ( 'CAPPOEXT_SUPPLIER_LIST', sysdate, l_processed_count_s, l_rejected_count_s, l_processed_count_s-l_rejected_count_s, l_err_msg );
  
  commit;           
      
  utl_file.fclose(l_utl_file);
 exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;
end;
/


--exit
